package com.bw.mapper;

import com.bw.entity.Vo;
import com.bw.entity.Vo1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author LQ&LYPlq
 * @ClassName VoMapper
 * @description: TODO
 * @date 2024年12月31日
 * @version: 1.0
 */
@Mapper
public interface VoMapper {

    /**
     * 统计当日下单总金额（周期：当日，指标：金额）当日：2023-01-06
     * @return
     */
    @Select("select    sum(order_info.amount) \n" +
            "\t     from   order_info   where   date(order_info.create_time)='2023-01-06' ")
    public  Double  getList1();

    /**
     * 统计各类货物下单金额占比存储过程 饼状图展示
     * @return
     */
    @Select("  call  sp3() ")
    public List<Vo> getList2();

    /**
     * 统计各机构完成运输次数排名 轮播表格展示
     */
    @Select(" select\n" +
            "     a.* ,RANK() over( order by a.num)\t \t\t \n" +
            "\t   from   (\n" +
            "    select   \n" +
            "\t\t     base_organ.org_name name,\n" +
            "\t\t\t\t sum(transport_task.actual_distance) value,\n" +
            "\t\t\t\t count(transport_task.id) num,\n" +
            "\t\t\t\t sum( TIMESTAMPDIFF(SECOND,transport_task.actual_start_time,transport_task.actual_end_time)) totalTime,\n" +
            "\t\t\t\t sum( TIMESTAMPDIFF(SECOND,transport_task.actual_start_time,transport_task.actual_end_time)) /count(transport_task.id) avgTime,\n" +
            "\t\t\t\t sum(transport_task.actual_distance)/count(transport_task.id) avgdistance\n" +
            " \t\t     from   transport_task \n" +
            "\t\t\t\t left  join   line_base_info  on  transport_task.line_id=line_base_info.id\n" +
            "\t\t\t\t left  join  base_organ  on   line_base_info.org_id=base_organ.id\n" +
            "\t\t\t\t group by   base_organ.org_name) a ")
    public  List<Vo1>  getList3();

    /**
     * 统计各省份分拣次数
     */
    @Select(" select    \n" +
            "\t\t      b2.`name` name,\n" +
            "\t\t\t\t\tcount(transport_task.id) value\t\t\t\t\t\n" +
            "\t\t      from    transport_task   \n" +
            "\t\t\t\t\tleft  join  line_base_info  on   transport_task.line_id =line_base_info.id\n" +
            "\t\t\t\t\tleft  join  base_organ   on   line_base_info.org_id=base_organ.id\n" +
            "\t\t\t\t\tleft  join   base_region_info b1 on  b1.id=base_organ.region_id\n" +
            "\t\t\t\t\tleft  join   base_region_info b2 on  b2.id=b1.parent_id \n" +
            "\t\t\t\t\tGROUP BY    b2.`name` ")
    public  List<Vo>  getList4();


}
